package com.xr.oa.vote;

import java.text.SimpleDateFormat;

import com.bstek.dorado.data.*;
import com.bstek.dorado.data.db.SqlDataset;
import com.bstek.dorado.common.*;
import com.xr.common.CommUtil;
import com.xr.common.Tools;

/**
 * datasetListener
 */
public class datasetListener extends AbstractDatasetListener {
  public boolean beforeLoadData(Dataset dataset)
     throws Exception {
	  String className = dataset.parameters().getString("className");
		String sql = "";
		SqlDataset ds = (SqlDataset) dataset;
		
		if(className != null && className.equals("voteDeptRoleSet_datasetCom")) {
			String fileId=Tools.toStr(dataset.parameters().getString("VOTE_ID"));
			String superComId=Tools.toStr(dataset.parameters().getString("super_company_id"));
			if (!superComId.equals("") &&  !superComId.equals("null"))  {
				superComId="com.super_company_id="+superComId;
			}else{
				superComId="com.super_company_id is null";
			}
			sql="select distinct 1 ischecked, com.company_id,com.company_name from org_company com,oa_vote_dept_role idr " +
					"where com.company_id=idr.dept_role_id and com.dr=0 and type=4 " +
					"and idr.VOTE_ID="+fileId +
					" and "+superComId+" union " +
					"select distinct 0 ischecked, com.company_id,com.company_name from org_company com where com.dr=0 and " +superComId+
					" and com.company_id not in (select dept_role_id from oa_vote_dept_role " +
					"where VOTE_ID="+fileId+" and type=4 )";

			ds.setSql(sql);
		 	}
       if(className != null && className.equals("voteDeptRoleSet_datasetDept")) {
    	   String companyId=Tools.toStr(dataset.parameters().getString("COMPANY_ID"));
    	   String superDeptId=Tools.toStr(dataset.parameters().getString("SUPER_DEPT_ID"));
    	   String fileId=Tools.toStr(dataset.parameters().getString("VOTE_ID"));
    		if (!superDeptId.equals("") &&  !superDeptId.equals("null"))  {
				superDeptId="dept.super_dept_id="+superDeptId;
			}else{
				superDeptId="dept.super_dept_id is null";
			}
    	   
    	    
   		sql="select distinct 1 ischecked, dept.dept_id,dept.dept_name,dept.company_id from org_dept dept,oa_vote_dept_role idr " +
		"where dept.dept_id=idr.dept_role_id and dept.dr=0 and type=1 and dept.company_id= " +companyId+
		"and idr.VOTE_ID="+fileId +
		" and "+superDeptId+" union " +
		"select distinct 0 ischecked, dept.dept_id,dept.dept_name,dept.company_id from org_dept dept where dept.dr=0 and dept.company_id="+companyId+ 
		" and " +superDeptId+
		" and dept.dept_id not in (select dept_role_id from oa_vote_dept_role " +
		"where VOTE_ID="+fileId+" and type=1 )";

ds.setSql(sql);
    	    
		}
       
       if(className != null && className.equals("voteDeptRoleSet_datasetEmp")) {
    	   String fileId=Tools.toStr(dataset.parameters().getString("VOTE_ID"));
    	   String deptId=Tools.toStr(dataset.parameters().getString("DEPT_ID"));
    	    
    	 
			sql="select emp.employee_id,emp.employee_name,1 isChecked" +
					" from oa_vote_dept_role idr,org_employee emp,org_onoff_station oos where " +
					" emp.employee_id=oos.employee_id and emp.employee_id=idr.dept_role_id and idr.type=3 and idr.VOTE_ID="+fileId +
					" and oos.dept_id="+deptId+" union " +
					" select emp.employee_id,emp.employee_name,0 isChecked" +
					" from org_employee emp,org_onoff_station oos where oos.dept_id=" + deptId+
					" and emp.employee_id=oos.employee_id and emp.employee_id not in(select dept_role_id from oa_vote_dept_role " +
					"where VOTE_ID="+fileId+" and type=3 )";
			ds.setSql(sql);
    	   
		}

		
       if(className != null && className.equals("voteDeptRoleSet_datasetPosition")) {
    	   String fileId=Tools.toStr(dataset.parameters().getString("VOTE_ID"));

			sql="select p.position_id,p.position_name,1 isChecked" +
					" from pb_position p,oa_vote_dept_role idr where " +
					" p.position_id=idr.dept_role_id and idr.type=2 and idr.VOTE_ID="+fileId +
					" union " +
					" select p.position_id,p.position_name,0 isChecked" +
					" from pb_position p where " +
					" p.position_id not in(select dept_role_id from oa_vote_dept_role " +
					"where VOTE_ID="+fileId+" and type=2 )"; 
		 
    	   ds.setSql(sql);
       }
       
    if(className != null && className.equals("viewResult_datasetVoteSelect")) {
    	
    	String superVoteId=Tools.toStr(dataset.parameters().getString("SUPER_VOTE_ID"));
    	String voteId=Tools.toStr(dataset.parameters().getString("VOTE_ID"));
		if (!superVoteId.equals("") &&  !superVoteId.equals("null"))  {
			sql="select select_content SUBJECT,'0' vote_id,vs.vote_select_id," +
					"(select count(1) from oa_vote_select_emp vse where vse.vote_select_id=vs.vote_select_id)selectsum," +
					"(select count(distinct employee_id) from oa_vote_select_emp vse where vse.vote_id=vs.vote_id)votesum," +
					"((select count(1) from oa_vote_select_emp vse where vse.vote_select_id=vs.vote_select_id)/(select decode(count(distinct employee_id),0,1,count(distinct employee_id)) from oa_vote_select_emp vse where vse.vote_id=vs.vote_id)*100) voterate," +
					"(select if_open from oa_vote where vote_id="+superVoteId+")IF_OPEN " +
					"from oa_vote_select vs where vote_id="+superVoteId+"";
		}else{ 
			sql="select v.SUBJECT,v.vote_id  " +
			"from oa_vote v where v.vote_type='checkbox' start with v.vote_id="+voteId+" connect by prior v.vote_id=v.super_vote_id ";
  
		}
		 ds.setSql(sql);
      }
    if(className != null && className.equals("viewResult_datasetText")) {
    	DoradoContext context = DoradoContext.getContext();
  
    //	System.out.println("======"+context.getAttribute("common.defaultCharsetName"));
    	String superVoteId=Tools.toStr(dataset.parameters().getString("SUPER_VOTE_ID"));
    	String voteId=Tools.toStr(dataset.parameters().getString("VOTE_ID"));
		if (!superVoteId.equals("") &&  !superVoteId.equals("null"))  {
			//sql="select vote_text SUBJECT,'0' vote_id  from oa_vote_select_emp where vote_id="+superVoteId+"";
			sql="select vs.select_content SUBJECT,vse.vote_text,'0' vote_id from oa_vote_select vs,oa_vote_select_emp vse where vs.vote_select_id=vse.vote_select_id and vs.vote_id ="+superVoteId+" ";
			
		}else{
			//sql="select vs.select_content,vse.vote_text from oa_vote_select vs,oa_vote_select_emp vse where vs.vote_select_id=vse.vote_select_id and vs.vote_id in(select vote_id from oa_vote where vote_type='textarea' start with vote_id="+voteId+" connect by prior vote_id=super_vote_id) ";
			sql="select v.SUBJECT ,v.vote_id  " +
			"from oa_vote v where v.vote_type='textarea' start with v.vote_id="+voteId+" connect by prior v.vote_id=v.super_vote_id ";
  
		}
		 ds.setSql(sql);
      }
    if(className != null && className.equals("myVoteList_datasetVote")) {
    	sql=ds.getSql();
    	sql+=CommUtil.setConditionSQL(ds);
    	ds.setSql(sql);
    }
 	 
    return true;
  }

}
